package com.zhufei.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.zhufei.entity.HomePageImage;
import com.zhufei.entity.News;

public class HomePageImageDao extends DBCon{
	private static String TABLE = "zhufei_home_image";
	private static String ID = "ID";
	private static String TITLE = "title";
	private static String IMAGE_PATH = "image_path";
	private static String URL = "url";
	private static String TIME = "time";

	/**
	 * 插入一条主页图片到数据库，插入时间自动生成
	 * @param title   标题
	 * @param image_path  图片路径
	 * @param url  超链接
	 * @return
	 */
	public static boolean insertImage(String title, String image_path, String url){
		DBCon con = new DBCon();
		try {
			String id = GlobalsDao.getUUID();
			String dt = GlobalsDao.getCurrentDateTime();
			Statement statement = con.getStatement();
			String sql = "insert into " + TABLE + " ("+ID+","+TITLE+","+IMAGE_PATH+","+URL+","+TIME+") values ('"+id+"','"+title+"','"+image_path+"','"+url+"','"+dt+"')";
			statement.execute(sql);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return false;
	}
	
	/**
	 * 获取数据库中首页图片总数
	 * @return  返回数据库有多少条图片记录
	 */
	public static int getCountOfImages(){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "select count(*) as count from "+TABLE;
			ResultSet rs = statement.executeQuery(sql);
			if(rs.next()){
				int count = rs.getInt("count");
				rs.close();
				return count;
			}
			else {
				rs.close();
				return 0;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		} 
		return 0;
	}
	
	/**
	 * 删除
	 * @param ID      ID
	 * @return  true表示成功，false表示失败
	 */
	public static boolean deleteImage(String ID){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "delete from "+TABLE+" where ID ='"+ID+"'";
			statement.executeUpdate(sql);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return false;
	}
	
	/**
	 * 按照时间降序的方式获取一定数量的图片
	 * @param   从第index条开始获取（第一条下标是0）
	 * @param   获取新闻的数量，输入-1则获取数据库中所有图片
	 * @return  新闻对象的ArrayList
	 */
	public static ArrayList<HomePageImage> getImages(int index, int number){
		ArrayList<HomePageImage> imagesList = new ArrayList<HomePageImage>();
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = null;
			if(number != -1){
				sql = "select * from "+ TABLE +" order by "+TIME+" desc limit "+index+","+number;
			}else{
				sql = "select * from "+ TABLE +" order by "+TIME+" desc";
			}
			
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()){
				HomePageImage image = new HomePageImage();
				image.setID(rs.getString(ID));
				image.setTitle(rs.getString(TITLE));
				image.setImage_path(rs.getString(IMAGE_PATH));
				image.setUrl(rs.getString(URL));
				image.setTime(rs.getString(TIME));
				imagesList.add(image);
			}
			rs.close();
			return imagesList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return null;
	}
}
